import { defineStore } from 'pinia'
import DatabaseService from '@/services/database'

export const useTableDataStore = defineStore('tableData', {
  state: () => ({
    // 表数据缓存
    dataCache: {},
    // 加载状态
    loading: false,
    // 错误信息
    error: null
  }),

  getters: {
    // 获取缓存的表数据
    getCachedData: (state) => (configId, database, table) => {
      const key = `${configId}_${database}_${table}`
      return state.dataCache[key] || null
    }
  },

  actions: {
    // 获取表数据
    async fetchTableData({ configId, database, table, page = 1, pageSize = 50, search = '', filters = [] }) {
      this.loading = true
      this.error = null
      
      try {
        // 获取表列信息
        const columns = await DatabaseService.getTableColumns(configId, database, table)
        
        // 构建查询 SQL
        let sql = `SELECT * FROM \`${database}\`.\`${table}\``
        const conditions = []
        
        // 添加搜索条件
        if (search) {
          const searchConditions = columns
            .filter(col => ['varchar', 'text', 'char'].includes(col.Type.toLowerCase()))
            .map(col => `\`${col.Name}\` LIKE '%${search}%'`)
          
          if (searchConditions.length > 0) {
            conditions.push(`(${searchConditions.join(' OR ')})`)
          }
        }
        
        // 添加筛选条件
        if (filters && filters.length > 0) {
          filters.forEach(filter => {
            if (filter.column && filter.operator && filter.value !== undefined) {
              const value = filter.operator.includes('LIKE') ? `'%${filter.value}%'` : `'${filter.value}'`
              conditions.push(`\`${filter.column}\` ${filter.operator} ${value}`)
            }
          })
        }
        
        // 添加 WHERE 子句
        if (conditions.length > 0) {
          sql += ` WHERE ${conditions.join(' AND ')}`
        }
        
        // 获取总行数
        const countSql = `SELECT COUNT(*) as total FROM \`${database}\`.\`${table}\`${conditions.length > 0 ? ' WHERE ' + conditions.join(' AND ') : ''}`
        const countResult = await DatabaseService.executeQuery(configId, countSql)
        const total = countResult.rows?.[0]?.total || 0
        
        // 添加分页
        const offset = (page - 1) * pageSize
        sql += ` LIMIT ${pageSize} OFFSET ${offset}`
        
        // 执行查询
        const result = await DatabaseService.executeQuery(configId, sql)
        
        if (result.Error) {
          throw new Error(result.Error)
        }
        
        // 格式化列信息
        const formattedColumns = columns.map(col => ({
          field: col.Name,
          label: col.Name,
          type: col.Type,
          primaryKey: col.IsPrimaryKey || false,
          notNull: !col.IsNullable,
          readonly: col.Extra?.includes('GENERATED') || false,
          sortable: true,
          minWidth: 100
        }))
        
        // 缓存数据
        const key = `${configId}_${database}_${table}`
        this.dataCache[key] = {
          data: result.rows || [],
          columns: formattedColumns,
          total,
          page,
          pageSize,
          timestamp: Date.now()
        }
        
        return {
          data: result.rows || [],
          columns: formattedColumns,
          total,
          page,
          pageSize
        }
      } catch (error) {
        this.error = error.message
        throw error
      } finally {
        this.loading = false
      }
    },

    // 更新表单元格
    async updateTableCell({ configId, database, table, rowId, field, value }) {
      try {
        // 获取主键列
        const columns = await DatabaseService.getTableColumns(configId, database, table)
        const primaryKeys = columns.filter(col => col.IsPrimaryKey).map(col => col.Name)
        
        if (primaryKeys.length === 0) {
          throw new Error('表没有主键，无法更新')
        }
        
        // 构建更新 SQL
        const valueStr = value === null ? 'NULL' : `'${String(value).replace(/'/g, "''")}'`
        const whereConditions = primaryKeys.map(pk => `\`${pk}\` = '${rowId}'`).join(' AND ')
        const sql = `UPDATE \`${database}\`.\`${table}\` SET \`${field}\` = ${valueStr} WHERE ${whereConditions}`
        
        // 执行更新
        const result = await DatabaseService.executeNonQuery(configId, sql)
        
        if (result.Error) {
          throw new Error(result.Error)
        }
        
        // 更新缓存
        const key = `${configId}_${database}_${table}`
        if (this.dataCache[key]) {
          const rowIndex = this.dataCache[key].data.findIndex(row => {
            return primaryKeys.every(pk => row[pk] === rowId)
          })
          
          if (rowIndex !== -1) {
            this.dataCache[key].data[rowIndex][field] = value
          }
        }
        
        return result
      } catch (error) {
        this.error = error.message
        throw error
      }
    },

    // 删除表行
    async deleteTableRow({ configId, database, table, rowId }) {
      try {
        // 获取主键列
        const columns = await DatabaseService.getTableColumns(configId, database, table)
        const primaryKeys = columns.filter(col => col.IsPrimaryKey).map(col => col.Name)
        
        if (primaryKeys.length === 0) {
          throw new Error('表没有主键，无法删除')
        }
        
        // 构建删除 SQL
        const whereConditions = primaryKeys.map(pk => `\`${pk}\` = '${rowId}'`).join(' AND ')
        const sql = `DELETE FROM \`${database}\`.\`${table}\` WHERE ${whereConditions}`
        
        // 执行删除
        const result = await DatabaseService.executeNonQuery(configId, sql)
        
        if (result.Error) {
          throw new Error(result.Error)
        }
        
        // 更新缓存
        const key = `${configId}_${database}_${table}`
        if (this.dataCache[key]) {
          this.dataCache[key].data = this.dataCache[key].data.filter(row => {
            return !primaryKeys.every(pk => row[pk] === rowId)
          })
          this.dataCache[key].total--
        }
        
        return result
      } catch (error) {
        this.error = error.message
        throw error
      }
    },

    // 插入表行
    async insertTableRow({ configId, database, table, data }) {
      try {
        // 构建插入 SQL
        const fields = Object.keys(data).filter(key => data[key] !== undefined)
        const values = fields.map(field => {
          const value = data[field]
          return value === null ? 'NULL' : `'${String(value).replace(/'/g, "''")}'`
        })
        
        const sql = `INSERT INTO \`${database}\`.\`${table}\` (\`${fields.join('`, `')}\`) VALUES (${values.join(', ')})`
        
        // 执行插入
        const result = await DatabaseService.executeNonQuery(configId, sql)
        
        if (result.Error) {
          throw new Error(result.Error)
        }
        
        // 清除缓存，下次会重新加载
        const key = `${configId}_${database}_${table}`
        delete this.dataCache[key]
        
        return result
      } catch (error) {
        this.error = error.message
        throw error
      }
    },

    // 导出表数据
    async exportTableData({ configId, database, table, format, selection = null }) {
      try {
        let data = []
        
        if (selection && selection.length > 0) {
          // 导出选中的数据
          data = selection
        } else {
          // 导出所有数据
          const sql = `SELECT * FROM \`${database}\`.\`${table}\``
          const result = await DatabaseService.executeQuery(configId, sql)
          
          if (result.Error) {
            throw new Error(result.Error)
          }
          
          data = result.rows || []
        }
        
        if (data.length === 0) {
          throw new Error('没有可导出的数据')
        }
        
        // 根据格式导出
        switch (format) {
          case 'csv':
            await this.exportAsCSV(data, `${database}_${table}`)
            break
          case 'json':
            await this.exportAsJSON(data, `${database}_${table}`)
            break
          case 'excel':
            // TODO: 实现 Excel 导出
            throw new Error('Excel 导出功能正在开发中')
          default:
            throw new Error(`不支持的导出格式: ${format}`)
        }
      } catch (error) {
        this.error = error.message
        throw error
      }
    },

    // 导出为 CSV
    async exportAsCSV(data, filename) {
      if (data.length === 0) return
      
      // 获取列名
      const headers = Object.keys(data[0])
      
      // 构建 CSV 内容
      const csvContent = [
        headers.join(','),
        ...data.map(row => {
          return headers.map(header => {
            const value = row[header]
            if (value === null || value === undefined) return ''
            if (typeof value === 'string' && value.includes(',')) {
              return `"${value.replace(/"/g, '""')}"`
            }
            return value
          }).join(',')
        })
      ].join('\n')
      
      // 下载文件
      const blob = new Blob(['\ufeff' + csvContent], { type: 'text/csv;charset=utf-8;' })
      const url = URL.createObjectURL(blob)
      const link = document.createElement('a')
      link.href = url
      link.download = `${filename}.csv`
      document.body.appendChild(link)
      link.click()
      document.body.removeChild(link)
      URL.revokeObjectURL(url)
    },

    // 导出为 JSON
    async exportAsJSON(data, filename) {
      const jsonContent = JSON.stringify(data, null, 2)
      
      // 下载文件
      const blob = new Blob([jsonContent], { type: 'application/json;charset=utf-8;' })
      const url = URL.createObjectURL(blob)
      const link = document.createElement('a')
      link.href = url
      link.download = `${filename}.json`
      document.body.appendChild(link)
      link.click()
      document.body.removeChild(link)
      URL.revokeObjectURL(url)
    },

    // 清除缓存
    clearCache(configId = null, database = null, table = null) {
      if (configId && database && table) {
        const key = `${configId}_${database}_${table}`
        delete this.dataCache[key]
      } else if (configId) {
        // 清除特定连接的所有缓存
        Object.keys(this.dataCache).forEach(key => {
          if (key.startsWith(`${configId}_`)) {
            delete this.dataCache[key]
          }
        })
      } else {
        // 清除所有缓存
        this.dataCache = {}
      }
    }
  }
}) 